{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [],
   "source": [
    "from data_define import Record\n",
    "from file_define import TextFileReader,JsonFileReader\n",
    "from pymysql import Connection\n",
    "import json"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 数据准备"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 文件读取到python\n",
    "text_file_read = TextFileReader('../data/2011年1月销售数据.txt')\n",
    "json_file_read = JsonFileReader('../data/2011年2月销售数据JSON.txt')\n",
    "\n",
    "jan_data: list[Record] = text_file_read.reader_data()\n",
    "feb_data: list[Record] = json_file_read.reader_data()\n",
    "\n",
    "# 将2个月的数据合并为1个list来存储\n",
    "all_data: list[Record] = jan_data + feb_data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 链接数据库"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 获取到MySql数据库的链接对象\n",
    "conn = Connection(\n",
    "\thost = 'localhost',\t# 主机名(或IP地址)\n",
    "    port = 3306,\t\t# 端口，默认3306\n",
    "    user = 'root',\t\t# 账户名\n",
    "    password = 'xf538013chao', \t# 密码\n",
    "    autocommit=True,\t\t# 设置自动提交\n",
    ")\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 在Mysql中创建数据库和表"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# use market    # 进入 market数据库\n",
    "\n",
    "# # 创建表1、2月份销售数据\n",
    "# create table Sales_in_January_and_February(\n",
    "# \torder_date date,\n",
    "# \torder_id varchar(255),\n",
    "# \tmoney float,\n",
    "# \tprovince varchar(10)\n",
    "# )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 插入数据到数据库"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 获取游标对象\n",
    "cursor = conn.cursor()\n",
    "# 选择数据库\n",
    "conn.select_db(\"market\")\n",
    "\n",
    "# 执行SQL语句,向创建好的mysql加入数据\n",
    "for record in all_data:\n",
    "    insert = f\"insert into Sales_in_January_and_February(order_date,order_id,money,province)\\\n",
    " values('{record.date}','{record.order_id}','{record.money}','{record.province}')\"      # 注意:此处数据需要打引号！！！\n",
    "    # print(insert)\n",
    "    # 插入数据\n",
    "    cursor.execute(insert)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 将写入的Mysql的数据通过python代码读取出来"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 获取游标对象\n",
    "cursor = conn.cursor()\n",
    "# 选择数据库\n",
    "conn.select_db(\"market\")\n",
    "\n",
    "# 执行SQL语句读取Mysql数据\n",
    "cursor.execute(\"select * from Sales_in_January_and_February\")\n",
    "results = cursor.fetchall()\n",
    "\n",
    "# 创建一个列表，以便转为json数据\n",
    "json_list = []\n",
    "for result in results:\n",
    "    # print(str(result[0]))\n",
    "    tuple_data = ((\"date\",str(result[0])),(\"orderid\",result[1]),(\"moeny\",result[2]),(\"province\",result[3])) # 转为元组数据\n",
    "    dict_data = dict(tuple_data)    # 转为字典数据\n",
    "    json_list.append(dict_data)\n",
    "# 通过json.dumps(data)方法,ensure_ascii=False为正常显示中文\n",
    "json_data = json.dumps(json_list,ensure_ascii=False)\n",
    "\n",
    "path = \"../tmp/json_data.json\"\n",
    "# 将数据写入文件中\n",
    "f = open(path,\"w\",encoding='utf8')\n",
    "f.write(json_data)\n",
    "f.close()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.9.13 ('base')",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.13"
  },
  "orig_nbformat": 4,
  "vscode": {
   "interpreter": {
    "hash": "79c2bc36f36ebca92fe5f3c73355a3866f3a34a4fd0b1d43087d5313dd5fd5ff"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
